<?php
/**
 * Accessor for SQL SELECT queries.
 * $sql = "SELECT * FROM table";
 * $result = new DbResult($sql);
 *
 * uses standard MySQL library actually
 * @todo implement iterator http://fr.php.net/manual/fr/language.oop5.iterations.php
 * @link http://webappkit.net
 * @package webappkit
 * @subpackage db
 * @author J.Ducastel <nospam0@ducastel.name>
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 */

class DbResult {

    /**
     * builds from a resource (as from mysql_query)
     * @access public
     * @static
     * @param resource $resource
     * @param string $sql
     * @return DbResult
     */
    function & fromResource(&$resource, $sql) {
        $result = new DbResult($sql);
        $result->setResource($resource);
        return $result;
    }

    /**
     * constructor
     * @param string $sql SQL SELECT query to execute
     */
    function DbResult($sql) {
        $this->sql=$sql;
    }

    /**
     * fetch a line as associative array.
     * @access public
     * @return array
     */
    function fetchAssoc() {
        if (!$res = & $this->getResource())
            return false;
        return mysql_fetch_assoc($res);
    }

    /**
     * return lines count
     * if not buffered and lines remain, would be incorrect
     * @access public
     * @return int
     */
    function count() {
        if (!$res = &$this->getResource())
            return false;
        // if not buffered and lines remain, would be incorrect
        /*if (!$this->buffered and !$this->lines_remaining)
            return false;*/
        return mysql_num_rows($res);
    }

    /**
     * move to line $n (starting 0)
     * @param int $line
     * @return bool
     */
    public function dataSeek($line) {
        return false;
    }

    /**
     * get pages count having $lines lines per page, skip to page $page
     * @access public
     * @param int $lines lines per page
     * @param int $page go to page
     * @return int pages count
     */
    function paginate($lines, $page=1) {
        $pages = ceil($this->count() / $lines); // echo $pages;

        return $pages;
    }

    /**
     * return resource. execute query if necessary.
     * @return resource
     */
    function & getResource() {
        static $n = 0;
        if (!$this->resource and $n<50) {
            // $this->resource=mysql_unbuffered_query($this->sql);
            $this->resource = mysql_query($this->sql);
            // print_r($this->resource); echo "<p>fetched resource #$n</p>";
            if (!$this->resource) {
                $this->failed = true;
                $this->error=mysql_error();
            }
            $n++;
        }
        return $this->resource;
    }

    /**
     * sets result resource
     * @param resource $result as from mysql_query()
     * @return bool
     */
    function setResource(&$result) {
        $this->resource = &$result;
        $this->failed = $result?true:false;
        return true;
    }

    /**
     * @return string sql error if occured
     */
    function getError() {
        return $this->error;
    }

    /**
     * @return bool
     */
    function failed() {
        return $this->failed;
    }

    /**
     * force download as a CSV file
     * wont send anything if result is empty
     * @return int lines sent
     */
    function downloadAsCsv($filename, $separator=';', $enclosure='"') {
        // check result
        if (!$this->count())
            return 0;
        // sending headers
        $this->_sendDownloadHeaders($filename.'.csv');
        // building header line
        $line = $this->fetchAssoc();
        echo $this->_buildCsvLine(array_keys($line), $separator, $enclosure);
        // first data line
        echo $this->_buildCsvLine($line, $separator, $enclosure);
        $count = 1;
        // remaining lines
        while ($line = $this->fetchAssoc()) {
            echo $this->_buildCsvLine($line, $separator, $enclosure);
            $count ++;
        }
        return $count;
    }

    /**
     * @var resource
     */
    var $resource;

    /**
     * @var string sql query executed
     */
    var $sql;

    /**
     * @var string SQL error if occured
     */
    var $error;

    /**
     * @var bool
     */
    var $failed;

    /**
     * @var bool if result is buffered
     */
    var $buffered=false;

    /**
     * envoie les headers http pour telechargement
     * @access private
     * @param string $name nom de fichier
     * @param int $size taille en octets
     * @return bool
     */
    function _sendDownloadHeaders($name,$size=NULL) {
        if (isset($_SERVER['HTTP_USER_AGENT']) and $_SERVER['HTTP_USER_AGENT']=="MSIE") // internet explorer
            header("Content-type: application/force-download");
        else // un bon navigateur ;o)
            header("Content-type: application/octet-stream");
        if ($size)
            header("Content-Length: ".$size);
        header("Content-Disposition: attachment; filename=".$name);
        return true;
    }

    /**
     * builds a CSV line from an array
     * @param array $line data line
     * @param string $separator separator character
     * @param string $enclosure enclosure character
     * @return string CSV line with
     */
    function _buildCsvLine($line, $separator, $enclosure, $remove_linebreaks=true) {
        $csv = '';
        foreach ($line as $value) {
            // escaping enclosure
            $value = str_replace($enclosure, '\\'.$enclosure, $value);
            // escaping separator
            $value = str_replace($separator, '\\'.$separator, $value);
            // removing line breaks
            if ($remove_linebreaks) {
                $value = str_replace(chr(13),' ',$value);
                $value = str_replace(chr(10),' ',$value);
            }
            // building line
            $csv .= $enclosure
            .$value
            .$enclosure
            .$separator;
        }
        // wiping last separator, adding CR+LF
        return substr($csv,0,-1)
        .chr(13).chr(10);
        // escaping enclosure char
        $data = str_replace($enclosure, '\\'.$enclosure, $data);
        return $enclosure
        .join($enclosure.$separator.$enclosure, $data)
        .$enclosure
        .chr(13).chr(10);
    }
}
